#!/usr/bin/python
# -*- coding: utf-8 -*-
# 曹志强
# 导出excel
# 2019.12.26
#from admin.company.models import ClockRecord
from django.db import models


from common.model_helper import get_model_fieldName
from openpyxl import Workbook, load_workbook

from django.http import HttpResponse


class ExportFile:

    def __init__(self, filename, obj_list, keys_list, verbose_list):
        """
        :param filename:  文件名
        :param obj_list:  对象
        :param keys_list:  key对应对象的每一个字段
        :param verbose_list: value对应verbose_name excel 第一行的每列表示
        """
        if not hasattr(obj_list[0], 'get'):
            raise AttributeError('请传入ORM字典类型格式 PS: objects values')

        self.filename = filename
        self.obj_list = obj_list
        self.wb = Workbook()
        self.ws = self.wb.active
        self.values_list = verbose_list
        self.key_list = keys_list

    def run(self):
        self.create_row()
        self.create_other_row()

    def create_row(self):
        for col_1 in range(1, len(self.values_list) + 1):
            self.ws.column_dimensions[string.ascii_uppercase[col_1 - 1]].width = 20
            self.ws.cell(row=1, column=col_1).value = self.values_list[col_1 - 1]

    def create_other_row(self):
        row_ = 2
        for obj in self.obj_list:
            for x in range(1, len(self.key_list) + 1):
                self.ws.cell(row_, x).value = obj[self.key_list[x - 1]]
            row_ += 1

    def save(self):
        # 保存
        self.wb.save(self.filename)
        self.wb.close()


def export_views(queryset,model,list_fields):
    wb = Workbook()  # optimized_write=True
    # excel名称基于创建时间命名
    sheet_name = "excel"
    # Basic_Information是我的model中的，你可以根据自己项目更改
    #basic_obj = ProjectInfo.objects.all()
    # 第一个sheet就是w1 = wb.create_sheet('sheet名称', 0)
    # 第二个sheet就是w2 = wb.create_sheet('sheet名称', 1)以此类推
    w1 = wb.create_sheet(model._meta.verbose_name, 0)
    # basicname_list是你在每个sheet的第一行插入的信息
    # basicname_list = ['...']
    for i in range(1, len(list_fields) + 1):
        field = list_fields[i-1]
        name = get_model_fieldName(model,field)
        w1.cell(row=1, column=i, value=name)
    excel_row = 2
    # 导入mysql中的数据
    for obj in queryset:
        for i in range(1, len(list_fields) + 1):
            key = list_fields[i-1]
            value = getattr(obj, key)
            if isinstance(value,models.Model):
                value = value.__str__()
            w1.cell(row=excel_row, column=i, value=value)
        excel_row += 1
    response = HttpResponse(content_type='application/msexcel')
    response['Content-Disposition'] = 'attachment;filename=%s.xlsx' % sheet_name
    wb.save(response)
    return response

def export_excel(self, request, queryset):
        return export_views(queryset,queryset[0],self.list_display)
export_excel.short_description = "导出"

# 导入打卡记录
def import_excel_dkjl(request, obj, change):
    wb = load_workbook(filename=obj.file.path)
    ws = wb.worksheets[0]
    sqllist = []
    for row in range(obj.numberstart-1, ws.max_row):
        # for header in headers:
        date = ws["E"][row].value
        date = date.replace("/","-")
        department = ws["B"][row].value
        shebei = ws["C"][row].value
        meng = ws["D"][row].value
        #sql = ClockRecord(teammember=obj.teammember, date=date, department=department, shebei=shebei, meng=meng)
        #sqllist.append(sql)
    #ClockRecord.objects.bulk_create(sqllist)
